"""
Author        yongfa
Date          2022-07-06 14:08:21
LastEditTime  2023-09-01 08:57:52
LastEditors   yongfa
Description   数据库插件
"""

import typing
import decimal
from contextlib import contextmanager
from datetime import datetime, date, time as d_time
import time
from urllib.parse import quote, urljoin
from sqlalchemy.dialects import mysql

from flask import current_app, g
from flask_sqlalchemy import BaseQuery, Pagination
from flask_sqlalchemy import SQLAlchemy as _SQLAlchemy
from sqlalchemy import Column, DateTime
from sqlalchemy.sql import func

from app.extensions.exception import NotFound
from app.extensions.utils import md5sum


class SQLAlchemy(_SQLAlchemy):
    @contextmanager
    def auto_commit(self):
        try:
            yield
            self.session.commit()  # 事务
        except Exception as e:
            self.session.rollback()  # 回滚
            raise e


class Query(BaseQuery):
    """自定义查询异常"""

    def filter_by(self, **kwargs):
        return super(Query, self).filter_by(**kwargs)

    def get_or_404(self, ident, e=None, error_code=None, message=None):
        rv = self.get(ident)  # 查询主键
        if not rv:
            raise e if e else NotFound(error_code=error_code, message=message)
        return rv

    def first_or_404(self, e=None, error_code=None, message=None):
        '''
        :param e: 异常(exception)
        :param error_code: 错误码
        :param message: 错误信息
        :return:
        '''
        rv = self.first()
        if not rv:
            raise e if e else NotFound(error_code=error_code, message=message)
        return rv

    def all(self):
        rv = list(self)
        return rv if len(rv) != 0 else []

    def all_or_404(self, e=None, error_code=None, message=None):
        rv = self.all()
        if not rv:
            raise e if e else NotFound(error_code=error_code, message=message)
        return rv

    def all_by_wrap(self, wrap='items'):
        rv = self.all()
        return {wrap: rv} if wrap else rv


# 初始化数据库模型
db = SQLAlchemy(query_class=Query)


class CRUDMixin(object):
    '''Mixin 添加CRUD操作: create, get(read), update, delete'''

    @classmethod
    def get(cls, **kwargs):
        '''查询'''
        return cls.query.filter_by(**kwargs).first()

    @classmethod
    def get_for_update(cls, **kwargs):
        """行锁"""
        return cls.query.filter_by(**kwargs).with_for_update().first()

    @classmethod
    def get_or_soft_delete(cls, **kwargs):
        """软删除"""
        kwargs.update({'delete_time': current_app.config.get('DEFAULT_DELETE_TIME')})
        return cls.query.filter_by(**kwargs).first()

    @classmethod
    def get_or_404(cls, e: Exception = None, error_code: int = None, message: str = None, **kwargs):
        '''查询，不存在则返回异常'''
        error_kwargs = dict(e=e, error_code=error_code, message=message)
        return cls.query.filter_by(**kwargs).first_or_404(**error_kwargs)

    @classmethod
    def get_all_404(cls, e: Exception = None, error_code: int = None, message: str = None, **kwargs):
        '''查询所有'''
        error_kwargs = dict(e=e, error_code=error_code, message=message)
        return cls.query.filter_by(**kwargs).all_or_404(**error_kwargs)

    @classmethod
    def get_all(cls, **kwargs):
        '''查询所有'''
        return cls.query.filter_by(**kwargs).all()

    @classmethod
    def create(cls, commit: bool = True, **kwargs):
        '''新增, 若有非空字段，谨慎使用'''
        instance = cls()
        for attr, value in kwargs.items():
            if hasattr(instance, attr):
                setattr(instance, attr, value)
        return instance.save(commit)

    @classmethod
    def validate_data(cls, args: dict) -> dict:
        """获取表字段，并除空值, 若有非空字段，谨慎使用"""
        instance = cls()
        data = {}
        for attr, value in args.items():
            if hasattr(instance, attr) and value is not None:
                data[attr] = value
        return data

    def update(self, commit: bool = True, **kwargs):
        '''更新'''
        for attr, value in kwargs.items():
            if hasattr(self, attr):
                setattr(self, attr, value)
        return self.save(commit)

    def save(self, commit: bool = True):
        '''保存'''
        db.session.add(self)
        if commit:
            try:
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                raise e
        return self

    @classmethod
    def upsert_one(cls, keys: list = [], data: dict = {}):
        """存在则更新(单条)"""
        instance = cls()
        conditions = {}
        for key in keys:
            if not hasattr(instance, key):
                raise ValueError("Invalid key {} for upsert".format(key))
            conditions[key] = data[key]

        obj = cls.query.filter_by(**conditions).first()
        if obj is None:
            obj = cls.create(**data)
        else:
            obj.update(**data)
        return obj

    @classmethod
    def upsert_many(cls, keys: list, data: typing.List[typing.Dict]):
        """存在则更新,不存在则插入(多条)

        Args:
            keys (list): 需要更新的字段
            data (typing.List[typing.Dict]): 数据列表

        Raises:
            ValueError: _description_
            e: _description_
        """
        if not data:
            return
        stmt = mysql.insert(getattr(cls, '__table__')).values(data)
        dup = {}
        for k in keys:
            if hasattr(cls, k):
                dup[k] = getattr(stmt.inserted, k)
            else:
                raise ValueError("Invalid key {} for upsert".format(k))

        stmt = stmt.on_duplicate_key_update(**dup)
        # print(stmt)
        try:
            db.session.execute(stmt)
            db.session.commit()
        except Exception as e:
            db.session.rollback()
            raise e

    def flush(self):
        """预提交"""
        db.session.add(self)
        db.session.flush()
        return self

    def soft_delete(self, commit: bool = True):
        '''软删除'''
        self.delete_time = datetime.now()
        if commit:
            return self.save(commit)
        return self

    def hard_delete(self):
        '''硬删除'''
        try:
            db.session.delete(self)
            db.session.commit()
        except Exception as e:
            db.session.rollback()
            raise e
        return self


class EntityModel(CRUDMixin, db.Model):
    '''实体业务类，提供软删除，及创建、更新、删除时间信息的crud model'''
    __abstract__ = True
    create_time = Column('create_time', DateTime, server_default=func.current_timestamp(), comment='创建时间')
    update_time = Column('update_time', DateTime, server_default=func.current_timestamp(),
                         onupdate=func.current_timestamp(), comment='更新时间')
    delete_time = Column('delete_time', DateTime, server_default='1970-01-01 00:00:00', comment='删除时间')

    # 对外输出时的对象描述
    def __repr__(self):
        class_name = self.__class__.__name__
        return '<{}: {}>'.format(class_name, self.id)

    @classmethod
    def column_dict(cls):
        """用于导入文件标题和字段名关联"""
        all_columns = cls.__table__.columns._all_columns
        result = {}
        for column in all_columns:
            result[column.comment] = column.key
        return result

    @classmethod
    def column_comment_dict(cls):
        """字段名和标记的说明"""
        all_columns = cls.__table__.columns._all_columns
        result = {}
        for column in all_columns:
            result[column.key] = column.comment
        return result

    @property
    def output_fields(self):
        return []

    @property
    def exclude_fields(self):
        return ['delete_time']

    def list_to_dict(self, output_fields=[], exclude_fields=[]):
        return self.unfold(output_fields=output_fields, exclude_fields=exclude_fields)

    def to_dict(self, output_fields=[], exclude_fields=[]):
        return self.unfold(output_fields=output_fields, exclude_fields=exclude_fields)

    def base_to_dict(self, output_fields=[], exclude_fields=[]):
        return self.unfold(output_fields=output_fields, exclude_fields=exclude_fields)

    def unfold(self, output_fields=[], exclude_fields=[]):

        table_columns = self.__table__.columns.keys()
        # 可选择返回字段
        if not output_fields:
            output_fields = self.output_fields
        if not exclude_fields:
            exclude_fields = self.exclude_fields
        if len(output_fields) > 0:
            columns = output_fields
        else:
            columns = table_columns
        item = {}
        for key in columns:
            if key in exclude_fields:
                continue
            if hasattr(self, key):
                value = getattr(self, key)

                if isinstance(value, datetime):
                    value = value.strftime('%Y-%m-%d %H:%M:%S')
                elif isinstance(value, date):
                    value = value.strftime('%Y-%m-%d')

                elif isinstance(value, d_time):
                    value = value.strftime('%H:%M:%S')

                elif isinstance(value, decimal.Decimal):
                    value = float(value)
                item[key] = value if value is not None else ''
        return item

    def get_loc_file_url(self, file_fields: list) -> dict:
        """获取本地文件地址"""
        prefix = current_app.config['STATIC_LOCAL_URL']
        upload_relative = current_app.config['PUBLIC_UPLOAD_RELATIVE_FOLDER']
        if not prefix:
            prefix = urljoin(g.host_url, upload_relative)
        data = {}
        for file in file_fields:
            if hasattr(self, file) and getattr(self, file):
                attr_file = getattr(self, file)
                if isinstance(attr_file, list):
                    file_list = []
                    for item in attr_file:
                        file_list.append(urljoin(prefix, item))
                elif isinstance(attr_file, str) and 'http://' not in attr_file:
                    file_list = urljoin(prefix, attr_file)

                data[file] = file_list
        return data

    def get_cdn_url(self, filename):
        """
        获取cdn地址,访问时间默认为30分钟
        详细： https://help.aliyun.com/document_detail/85114.html?spm=a2c4g.11186623.4.3.18d91e74YduZzr
        注意：若文件路径中存在中文，需要转义url编码
        """
        if 'http://' in filename:
            return filename
        filename = quote(filename)
        domain = current_app.config['CDN_URI']
        key = current_app.config['CDN_KEY']
        exp = current_app.config['CDN_EXPIRE']
        expire = int(time.time()) + (exp - 1800)
        nexp = datetime.datetime.fromtimestamp(expire).strftime('%Y%m%d%H%M')
        string = key + nexp + filename
        hashvalue = md5sum(string)
        return "%s/%s/%s%s" % (domain, nexp, hashvalue, filename)


def pagination_to_dict(pagination, output_fields=[], exclude_fields=[]):
    """分页提取数据"""
    res = {}
    if isinstance(pagination, Pagination):
        res['items'] = []
        for item in pagination.items:
            res['items'].append(item.list_to_dict(output_fields=output_fields, exclude_fields=exclude_fields))
        res['page'] = pagination.page
        res['pages'] = pagination.pages
        res['per_page'] = pagination.per_page
        res['total'] = pagination.total
        res['has_next'] = pagination.has_next
        res['has_prev'] = pagination.has_prev
    return res


def model_list_to_dict(items=[], output_fields=[], exclude_fields=[]):
    """循环提取数据"""
    result = []
    for item in items:
        result.append(item.list_to_dict(output_fields=output_fields, exclude_fields=exclude_fields))
    return result


# 定时任务
# class ApschedulerJob(db.Model):
#     __tablename__ = 'apscheduler_jobs'

#     id = db.Column(db.String(191, 'utf8mb4_bin'), primary_key=True)
#     next_run_time = db.Column(db.Float(asdecimal=True), index=True)
#     job_state = db.Column(db.LargeBinary, nullable=False)


# casbin模型
# class CasbinRule(db.Model):
#     __tablename__ = 'casbin_rule'

#     id = db.Column(db.Integer, primary_key=True)
#     ptype = db.Column(db.String(255, 'utf8mb4_bin'))
#     v0 = db.Column(db.String(255, 'utf8mb4_bin'))
#     v1 = db.Column(db.String(255, 'utf8mb4_bin'))
#     v2 = db.Column(db.String(255, 'utf8mb4_bin'))
#     v3 = db.Column(db.String(255, 'utf8mb4_bin'))
#     v4 = db.Column(db.String(255, 'utf8mb4_bin'))
#     v5 = db.Column(db.String(255, 'utf8mb4_bin'))
